SET NOCOUNT ON;

/********************************************************

SET THE TIMEFRAME TO COLLECT THE DATA
USE UTC DATE WITH THE FORMAT YYYY-MM-DD HH:MM:SS
OR 
*********************************************************/

DECLARE @StartDate AS DATETIME = DATEADD(HOUR, -11, GETUTCDATE()); 
--DECLARE @StartDate AS DATETIME = '2023-08-02 20:00:00';

DECLARE @EndDate AS DATETIME = DATEADD(HOUR,  -1, GETUTCDATE());
--DECLARE @EndDate AS DATETIME = '2023-08-02 21:06:28';

/********************************************************

TO SHOW FULL REPORT SET @FULLREPORT TO 1 
TO HIDE FULL REPORT SET @FULLREPORT TO 0

*********************************************************/

DECLARE @SHOWFULLREPORT AS BIT = 1;

/********************************************************

  GET EVENTS IN THE SPECIFIED TIMEFRAME

*********************************************************/
IF OBJECT_ID('tempdb..#events') IS NOT NULL
	DROP TABLE #events;

SELECT CAST(EVENT_TIME_UTC as DATETIME2(0)) as Event_Date, 
		COUNT(SERIALNUM) as Total_Entries 
INTO #events
FROM [ACCESSCONTROL].[dbo].[EVENTS]
WHERE EVENT_TIME_UTC BETWEEN @StartDate AND @EndDate
GROUP BY cast(EVENT_TIME_UTC as DATETIME2(0))

/********************************************************

  COUNT OF EVENTS PER SECOND / MINUTES / HOUR 

*********************************************************/

IF OBJECT_ID('tempdb..#TotalsPerSecond') IS NOT NULL
	DROP TABLE #TotalsPerSecond;

IF OBJECT_ID('tempdb..#TotalsPerHour') IS NOT NULL
	DROP TABLE #TotalsPerHour

IF OBJECT_ID('tempdb..#TotalsPerMinute') IS NOT NULL
	DROP TABLE #TotalsPerMinute

SELECT
		CAST(calendar.Date as DATE) as Event_Date, 
		DATEPART(HOUR,calendar.Date) as Hrs, 
		DATEPART(MINUTE, calendar.Date) as Mins,
		DATEPART(SECOND, calendar.Date) as Secs, 
		ISNULL(Total_Entries, 0 ) as Total_Entries 
INTO #TotalsPerSecond
FROM (SELECT  TOP (DATEDIFF(SECOND, @StartDate, @EndDate) + 1)
        Date = CAST (DATEADD(SECOND, ROW_NUMBER() 
		OVER(ORDER BY a.object_id) - 1, @StartDate)as datetime2(0))
		FROM    sys.all_objects a CROSS JOIN sys.all_objects b) as calendar
LEFT JOIN #events ON calendar.Date = #events.Event_Date
	
SELECT Event_Date, Hrs, SUM(Total_Entries) AS TotalEntries
	INTO #TotalsPerHour
	FROM #TotalsPerSecond
	GROUP BY Event_Date, Hrs;

SELECT Event_Date, Hrs, Mins, SUM(Total_Entries) AS TotalEntries
	 INTO #TotalsPerMinute
	 FROM #TotalsPerSecond
	 GROUP BY Event_Date, Hrs,Mins;


/********************************************************

CREATE SUMMARY TABLE

******************************************************/

IF OBJECT_ID('tempdb..#Summary') IS NOT NULL
	DROP TABLE #Summary

CREATE TABLE #Summary(
start_date DATETIME,		--'Start Date' 
end_date DATETIME,			--'End Date' 
server_name VARCHAR(255),	--'Server name' 
server_ip VARCHAR(255),		--'Server IP' 
max_events_per_hour int,    -- Max Events per hour
min_events_per_hour int,    -- Min Events per hour
max_events_per_min int,     -- Max Events per minute
min_events_per_min int,     -- Min Events per minute
avg_events_ps int,			--'Average number of events per second' 
med_events_ps int,			--'Median number of events per second' 
peak_events_ps int,			--'Peak number of events per second' 
min_events_per_sec int,		-- Min Events per minute' 
number_panels int,			--'Number of panels'
number_readers int,			--'Number of readers'
number_inputs int,			--'Number of inputs'
number_outputs int,			--'Number of outputs'
number_personel int,		--'Number of Personnel'
number_badges int			--'Number of Badges'
)


INSERT INTO #Summary (start_date, end_date, server_name)
SELECT	@StartDate,
		@EndDate,
		[LNLSTRING]
 FROM [AccessControl].[dbo].[LNLCONFIG]
 WHERE LNLCONFIGID = 240


UPDATE #Summary SET 
					max_events_per_hour = (SELECT MAX(TotalEntries) FROM #TotalsPerHour), 
					min_events_per_hour = (SELECT MIN(TotalEntries) FROM #TotalsPerHour), 
					max_events_per_min = (SELECT MAX(TotalEntries) FROM #TotalsPerMinute), 
					min_events_per_min = (SELECT MIN(TotalEntries) FROM #TotalsPerMinute), 
					min_events_per_sec = (SELECT MIN(Total_Entries) FROM #TotalsPerSecond), 
					peak_events_ps = (SELECT MAX(#TotalsPerSecond.Total_Entries) FROM #TotalsPerSecond), 
					avg_events_ps = (SELECT AVG(#TotalsPerSecond.Total_Entries) FROM #TotalsPerSecond),
					med_events_ps = (SELECT(
									 (SELECT MAX(Total_Entries) FROM
									   (SELECT TOP 50 PERCENT Total_Entries FROM #TotalsPerSecond ORDER BY Total_Entries) AS BottomHalf)
									 +
									 (SELECT MIN(Total_Entries) FROM
									   (SELECT TOP 50 PERCENT Total_Entries FROM #TotalsPerSecond ORDER BY Total_Entries DESC) AS TopHalf)
									) / 2 AS Median)

/********************************************************

  LIST OF EVENT TYPES

*********************************************************/

IF OBJECT_ID('tempdb..#Event_types') IS NOT NULL
	DROP TABLE #Event_types

SELECT [EVTYPEID] ,[EVTDESCR]
	INTO #Event_types
	FROM [AccessControl].[dbo].[EVENTYPE]
	ORDER BY [EVTYPEID]

/********************************************************

  COUNT OF ALL ACTIVE BADGES IN THE SYSTEM

*********************************************************/

IF OBJECT_ID('tempdb..#Badges') IS NOT NULL
	DROP TABLE #Badges

SELECT 
	(SELECT COUNT(*) FROM [AccessControl].[dbo].[EMP]) as Total_Employee_Records,
	SUM(CASE WHEN UPPER(bt.Name) = 'EMPLOYEE' THEN 1 ELSE 0 END) AS Total_Employee_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'VISITOR' THEN 1 ELSE 0 END) AS Total_Visitor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'EMPLOYEE' AND UPPER(s.Name) = 'ACTIVE' THEN 1 ELSE 0 END) AS Active_Employee_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'VISITOR' AND UPPER(s.Name) = 'ACTIVE'  THEN 1 ELSE 0 END) AS Active_Visitor_Badges
INTO #Badges
FROM [AccessControl].[dbo].[EMP] e
INNER JOIN [AccessControl].[dbo].[BADGE] b ON e.ID = b.EMPID
INNER JOIN [AccessControl].[dbo].[BADGETYP] bt ON b.[TYPE] = bt.ID
INNER JOIN [AccessControl].[dbo].[BADGSTAT] s ON b.[STATUS] = s.ID 


UPDATE #Summary SET 
		number_personel = (SELECT COUNT(*) FROM [AccessControl].[dbo].[EMP]),
		number_badges = (SELECT Total_Employee_Records FROM #Badges)

/********************************************************
  COUNT OF PANELS
*********************************************************/
UPDATE #Summary SET number_panels =
(
	SELECT COUNT(*) AS Panel_Count
	FROM [AccessControl].[dbo].[ACCESSPANE]
)

/********************************************************
  COUNT OF READERS
*********************************************************/

IF OBJECT_ID('tempdb..#Readers') IS NOT NULL
	DROP TABLE #Readers

SELECT COUNT(*) as Total_Readers,
	SUM(PAIRMASTER) AS Total_Master,
	SUM(PAIRSLAVE) AS Total_Slave,
	SUM(CASE WHEN PAIRMASTER = 0 AND PAIRSLAVE = 0 THEN 1 ELSE 0 END) AS Total_Stand_Alone
	INTO #Readers
	FROM [AccessControl].[dbo].[Reader]


UPDATE #Summary SET number_readers = 
	(SELECT Total_Readers FROM #Readers )

/********************************************************
  COUNT OF OUTPUTS
*********************************************************/

UPDATE #Summary SET number_outputs =
	(SELECT COUNT(*) AS Output_Pin_Count
	FROM [AccessControl].[dbo].[RELAYOUTPT])

/********************************************************
  COUNT OF INPUTS
*********************************************************/
UPDATE #Summary SET number_inputs =
	(SELECT COUNT(*) AS Input_Pin_Count
	FROM [AccessControl].[dbo].[ALARMINPUT])

/********************************************************

  GETS ALL EVENTS THAT OCCURRED AFTER SPECIFIED START DATE/TIME.

*********************************************************/
IF OBJECT_ID('tempdb..#AllEvents') IS NOT NULL
	DROP TABLE #AllEvents

SELECT EVENT_TIME_UTC, EVENTTYPE, ET.EVTDESCR
	INTO #AllEvents
	FROM [AccessControl].[dbo].[EVENTS] E
	INNER JOIN [AccessControl].[dbo].[EVENTYPE] ET ON  E.EVENTTYPE = ET.EVTYPEID
	WHERE EVENT_TIME_UTC > @StartDate


/********************************************************
  TOTAL NUMBER OF EVENTS
*********************************************************/
IF OBJECT_ID('tempdb..#TotalEvents') IS NOT NULL
	DROP TABLE #TotalEvents

SELECT COUNT(*) AS Total_Events
	INTO #TotalEvents
	FROM [AccessControl].[dbo].[EVENTS] E


/********************************************************
  NUMBER OF EVENTS BY TYPE
*********************************************************/
IF OBJECT_ID('tempdb..#EventsByType') IS NOT NULL
	DROP TABLE #EventsByType

SELECT ET.EVTDESCR AS Event_Type, count(*) AS Event_Count
	INTO #EventsByType
	FROM [AccessControl].[dbo].[EVENTS] E
	INNER JOIN [AccessControl].[dbo].[EVENTYPE] ET ON  E.EVENTTYPE = ET.EVTYPEID
	GROUP BY ET.EVTDESCR


/********************************************************
  COUNT OF EMPLOYEES THAT HAVE A PHOTO IMAGE OR THUMBNAIL
*********************************************************/
IF OBJECT_ID('tempdb..#EmployeesWithPhoto') IS NOT NULL
	DROP TABLE #EmployeesWithPhoto

SELECT COUNT(DISTINCT E.ID) AS Employees_With_Photo
	INTO #EmployeesWithPhoto
	FROM [AccessControl].[dbo].[EMP] E
	INNER JOIN [AccessControl].[dbo].[MMOBJS] M ON E.ID = M.EMPID
	WHERE ([TYPE] = 0 AND [OBJECT] = 1) --Photo
	OR ([TYPE]  = 2 AND [OBJECT] = 1) --Thumbnail


SELECT 
	start_date AS	'Start Date', 
	end_date AS 'End Date', 
	server_name AS 'Server name' ,
--	server_ip AS 'Server IP' ,
	max_events_per_hour AS 'Max Events per hour',
	min_events_per_hour AS 'Min Events per hour',
	max_events_per_min AS 'Max Events per minute',
	min_events_per_min AS 'Min Events per minute',
	avg_events_ps AS 'Average number of events per second', 
	med_events_ps AS 'Median number of events per second' ,
	peak_events_ps AS 'Peak number of events per second' ,
	number_panels AS 'Number of panels',
	number_readers AS 'Number of readers',
	number_inputs AS 'Number of inputs',
	number_outputs AS 'Number of outputs',
	number_personel AS 'Number of Personnel',
	number_badges AS 'Number of Badges'
FROM #Summary



IF (@SHOWFULLREPORT = 1)
BEGIN
 
	SELECT * FROM #TotalsPerHour ORDER BY Event_Date, Hrs;
	SELECT * FROM #TotalsPerMinute ORDER BY Event_Date, Hrs, Mins;
	SELECT * FROM #TotalsPerSecond ORDER BY Event_Date, Hrs, Mins, Secs;
	SELECT * FROM #Event_types
	SELECT * FROM #Readers
	SELECT * FROM #Badges
	SELECT * FROM #AllEvents
	SELECT * FROM #EventsByType
	SELECT * FROM #EmployeesWithPhoto
END

DROP TABLE #TotalsPerHour, #TotalsPerMinute, #TotalsPerSecond, #Readers, #AllEvents, #TotalEvents, #Event_types, #EventsByType, #Badges, #EmployeesWithPhoto